package c0907m.group2.aitsm.core.db;

import c0907m.group2.aitsm.core.entity.SemesterInfo;
import c0907m.group2.aitsm.util.Helper;

import java.sql.Date;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 * Author: LongDH
 * Date: 4/27/11
 * Time: 2:53 PM
 * <p/>
 * Interface for selecting, inserting, updating from Semesters table.
 */
public class SemesterDb extends BaseDb {
    private String insert = "INSERT INTO semesters (class_id, start_date, end_date, description, name) VALUES (?, ?, ?, ?, ?)";
    private String update = "UPDATE semesters SET class_id = ?, start_date = ?, end_date = ?, description = ?, active = ?, name = ? WHERE id = ?";

    /**
     * Insert a Semester into database.
     *
     * @param semester row's information to insert.
     * @throws SQLException if a database access error occurs.
     */
    public void insert(SemesterInfo semester) throws SQLException {
        try {
            connect();
            st = con.prepareStatement(insert);

            st.setInt(1, semester.getClassId());
            st.setDate(2, new Date(semester.getStartDate().getTime()));
            st.setDate(3, new Date(semester.getEndDate().getTime()));
            st.setString(4, semester.getDescription());
            st.setString(5, semester.getName());
            st.executeUpdate();
        } finally {
            close();
        }
    }

    /**
     * Update a Semester into database.
     *
     * @param semester row's information to insert.
     * @throws SQLException if a database access error occurs.
     */
    public void update(SemesterInfo semester) throws SQLException {
        try {
            connect();
            st = con.prepareStatement(update);

            doUpdate(semester);
        } finally {
            close();
        }
    }

    /**
     * Update a list of Semesters into database.
     *
     * @param semesters list of row's information to insert.
     * @throws SQLException if a database access error occurs.
     */
    public void update(List<SemesterInfo> semesters) throws SQLException {
        try {
            connect();
            st = con.prepareStatement(update);

            for (SemesterInfo semester : semesters) {
                doUpdate(semester);
            }
        } finally {
            close();
        }
    }

    /**
     * Get a Semester for the given ID.
     *
     * @param id the row's ID to select.
     * @return row's information found, or null if none.
     * @throws SQLException if a database access error occurs.
     */
    public SemesterInfo select(int id) throws SQLException {
        String select = "SELECT * FROM semesters WHERE id = " + id;
        try {
            connect();
            st = con.prepareStatement(select);
            rs = st.executeQuery();

            if (rs.next()) {
                return getResult();
            }

            return null;
        } finally {
            close();
        }
    }

    /**
     * Get a list of all Semesters.
     *
     * @return list of row's information found.
     * @throws SQLException if a database access error occurs.
     */
    public List<SemesterInfo> select() throws SQLException {
        return select(null);
    }

    /**
     * Get a list of Semesters for the given name.
     *
     * @param name the row's name to search.
     * @return list of row's information found.
     * @throws SQLException if a database access error occurs.
     */
    public List<SemesterInfo> select(String name) throws SQLException {
        return select(name, 0, null, null);
    }

    /**
     * Get a list of Semesters.
     *
     * @param name     the row's name to search. Null for all.
     * @param class_id the class's ID to search. Zero for all.
     * @param from     from this date. Null for all.
     * @param to       to this date. Null for all.
     * @return list of row's information found.
     * @throws SQLException if a database access error occurs.
     */
    public List<SemesterInfo> select(String name, int class_id, java.util.Date from, java.util.Date to) throws SQLException {
        String select = "SELECT * FROM semesters WHERE 1 = 1";
        if (name != null) select += " AND name LIKE '%" + name + "%'";
        if (class_id != 0) select += " AND class_id = " + class_id;
        if (from != null && to != null) {
            select += " AND (start_date >= '" + Helper.fromDate(from) + "' AND start_date <= '" + Helper.fromDate(to) + "')";
            select += " OR (end_date >= '" + Helper.fromDate(from) + "' AND end_date <= '" + Helper.fromDate(to) + "')";
        } else {
            if (from != null) select += " AND start_date >= '" + Helper.fromDate(from) + "'";
            if (to != null) select += " AND end_date <= '" + Helper.fromDate(to) + "'";
        }
        try {
            connect();
            st = con.prepareStatement(select);
            rs = st.executeQuery();

            List<SemesterInfo> list = new ArrayList<SemesterInfo>();

            while (rs.next()) {
                list.add(getResult());
            }

            return list;
        } finally {
            close();
        }
    }

    /**
     * Update a data object into database.
     *
     * @param semester the data object.
     * @throws SQLException if a database access error occurs.
     */
    private void doUpdate(SemesterInfo semester) throws SQLException {
        st.setInt(1, semester.getClassId());
        st.setDate(2, new Date(semester.getStartDate().getTime()));
        st.setDate(3, new Date(semester.getEndDate().getTime()));
        st.setString(4, semester.getDescription());
        st.setBoolean(5, semester.isActive());
        st.setString(6, semester.getName());
        st.setInt(7, semester.getId());
        st.executeUpdate();
    }

    /**
     * Build a data object from ResultSet.
     *
     * @return the data object.
     * @throws SQLException if a database access error occurs.
     */
    private SemesterInfo getResult() throws SQLException {
        SemesterInfo semester = new SemesterInfo();
        semester.setId(rs.getInt(1));
        semester.setName(rs.getString(2));
        semester.setClassId(rs.getInt(3));
        semester.setStartDate(rs.getDate(4));
        semester.setEndDate(rs.getDate(5));
        semester.setDescription(rs.getString(6));
        semester.setActive(rs.getBoolean(7));

        return semester;
    }
}
